﻿using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using System;
using System.Configuration;
using System.Data;
using System.IO;
using System.Text;

namespace _9jCardRegisterCollection.Helper
{
    public class ApplicationFormParam
    {
        public ApplicationFormParam()
        {
            dt = new DataTable();
        }

        public string Title { set; get; }
        public string SheetName { set; get; }
        public string FileFullPath { set; get; }

        public DataTable dt { set; get; }
    }
    /// <summary>
    /// 申请单生成
    /// </summary>
    public class ApplicationFormHelper
    {

        /// <summary>
        /// config
        /// </summary>
        public static Configuration config = System.Configuration.ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None);
        public static void Create(ApplicationFormParam param)
        {
            using (FileStream fs = new FileStream(param.FileFullPath, FileMode.Create, FileAccess.Write))
            {
                var workbook = new HSSFWorkbook();
                var sheet = workbook.CreateSheet(param.Title);

                var passe = config.AppSettings.Settings["sheetPasse"].Value;
                if (!string.IsNullOrWhiteSpace(passe)) sheet.ProtectSheet(passe);

                // title
                var titleStyle = workbook.CreateCellStyle();
                // 设置居中
                titleStyle.VerticalAlignment = VerticalAlignment.Center;
                titleStyle.Alignment = HorizontalAlignment.Center;
                var fontStyle = workbook.CreateFont();
                fontStyle.IsBold = true;
                fontStyle.FontName = "新宋体";
                fontStyle.FontHeightInPoints = 15;
                titleStyle.SetFont(fontStyle);
                titleStyle.WrapText = true;

                var titleRow = sheet.CreateRow(0);
                titleRow.Height = 40 * 20;
                var cellTitle = titleRow.CreateCell(0);
                cellTitle.SetCellValue(param.Title);
                cellTitle.CellStyle = titleStyle;
                sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, 5));

                // 设置字体
                var font = workbook.CreateFont();
                font.FontName = "新宋体";
                font.FontHeightInPoints = 11;


                var applicationDateRow = sheet.CreateRow(1);
                applicationDateRow.CreateCell(4).SetCellValue("填表日期：");
                applicationDateRow.CreateCell(5).SetCellValue(DateTime.Now.ToString("yyyy-MM-dd"));

                var borderStyle = workbook.CreateCellStyle();
                borderStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
                borderStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
                borderStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
                borderStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
                borderStyle.TopBorderColor = HSSFColor.Black.Index;
                borderStyle.RightBorderColor = HSSFColor.Black.Index;
                borderStyle.BottomBorderColor = HSSFColor.Black.Index;
                borderStyle.LeftBorderColor = HSSFColor.Black.Index;
                borderStyle.SetFont(font);
                borderStyle.WrapText = true;

                var headerDateRow = sheet.CreateRow(2);
                for (int i = 0; i < param.dt.Columns.Count; i++)
                {
                    var strColumnName = param.dt.Columns[i].ColumnName;
                    var cell = headerDateRow.CreateCell(i);
                    cell.CellStyle = borderStyle;
                    cell.SetCellValue(strColumnName);

                    int length = Encoding.Default.GetBytes(strColumnName).Length + 10;
                    sheet.SetColumnWidth(i, length * 256);
                }
                var rowIndex = 3;
                for (int i = 0; i < param.dt.Rows.Count; i++)
                {
                    var row = sheet.CreateRow(rowIndex);
                    for (int j = 0; j < param.dt.Columns.Count; j++)
                    {
                        var rowValue = param.dt.Rows[i][param.dt.Columns[j].ColumnName].ToString();
                        var cell = row.CreateCell(j);
                        cell.CellStyle = borderStyle;
                        cell.SetCellValue(rowValue);

                        //var columnWidth = sheet.GetColumnWidth(i);
                        //int length = Encoding.Default.GetBytes(rowValue).Length + 2;
                        //var newColumnWidth = length * 256;
                        //if (newColumnWidth > columnWidth) sheet.SetColumnWidth(i, newColumnWidth);
                    }
                    rowIndex++;
                }

                var cardAdminRow = sheet.CreateRow(rowIndex + 2);
                cardAdminRow.CreateCell(0).SetCellValue("证件专管员（姓名)");
                cardAdminRow.CreateCell(4).SetCellValue("联系电话");

                var unitPrincipalRow = sheet.CreateRow(rowIndex + 6);
                unitPrincipalRow.CreateCell(0).SetCellValue("申领单位主要负责人（签章）");
                var reviewedByRow = sheet.CreateRow(rowIndex + 10);
                reviewedByRow.CreateCell(0).SetCellValue("归口审核主要负责人（签字）");
                var accreditationReviewedByRow = sheet.CreateRow(rowIndex + 14);
                accreditationReviewedByRow.CreateCell(0).SetCellValue("公安特勤部门复核");
                workbook.Write(fs);
                fs.Flush(true);
            }

        }
    }
}
